package com.bilibili.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.bilibili.dao.BaseDao;
import com.bilibili.dao.VideoAndCategoryDao;
import com.bilibili.dto.VideoAndCategory;

public class VideoAndCategoryDaoImpl implements VideoAndCategoryDao {

	@Override
	public List<VideoAndCategory> selectMyAllsendVideo(VideoAndCategory vac, int pageNum, int pageSize) {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();//打开连接
		List<Object> queryList = new ArrayList<Object>();
		String sql = "SELECT v2.* FROM " + 
				"`user` u1 LEFT JOIN video v2 " + 
				"ON u1.user_id = v2.uploader_id " + 
				"WHERE u1.user_id = ? ";
		queryList.add(vac.getUserId());
		if(vac.getStatus() != -1) {
			sql+="AND v2.status = ? ";
			queryList.add(vac.getStatus());
		}else{
			sql+=" AND v2.status != 2 ";
		}
		if(vac.getCategoryId() != -1) {
			sql+="AND v2.category_id = ? ";
			queryList.add(vac.getCategoryId());
		}
		if(vac.getTitle() != null && !"".equals(vac.getTitle())) {
			sql+="AND v2.title like concat('%',?,'%') ";
			queryList.add(vac.getTitle());
		}
		if(vac.getSortOrder() != null && !"".equals(vac.getSortOrder())) {
			sql+= "ORDER BY "+vac.getSortOrder()+" DESC ";
		}
		sql+="LIMIT ?,?";
		List<VideoAndCategory> list = new ArrayList<VideoAndCategory>();
		PreparedStatement ps= null;
		ResultSet rs=null;
		try {
			ps= conn.prepareStatement(sql);
			for(int i = 0;i<queryList.size();i++) {
				ps.setObject(i+1, queryList.get(i));
			}
			ps.setInt(queryList.size()+1, (pageNum-1)*pageSize);
			ps.setInt(queryList.size()+2, pageSize);
			rs = ps.executeQuery();			
			while(rs.next()) {
			    VideoAndCategory v = new VideoAndCategory();
			    v.setVideoId(rs.getInt("video_id"));
			    v.setTitle(rs.getString("title"));
			    v.setDescription(rs.getString("description"));
			    v.setCoverUrl(rs.getString("cover_url"));
			    v.setVideoUrl(rs.getString("video_url"));
			    v.setCategoryId(rs.getInt("category_id"));
			    v.setUploaderId(rs.getInt("uploader_id"));
			    v.setCreateTime(rs.getDate("create_time"));
			    v.setDuration(rs.getInt("duration"));
			    v.setStatus(rs.getInt("status"));
			    v.setViewCount(rs.getInt("view_count"));
			    v.setLikeCount(rs.getInt("like_count"));
			    v.setCoinCount(rs.getInt("coin_count"));
			    v.setCollectCount(rs.getInt("collect_count"));
			    list.add(v);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(conn, ps,rs);
		}
		return list;
	}

	@Override
	public int selectMyAllsendVideoNum(VideoAndCategory vac) {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();//打开连接
		List<Object> queryList = new ArrayList<Object>();
		String sql = "SELECT count(1) as total FROM " + 
				"`user` u1 LEFT JOIN video v2 " + 
				"ON u1.user_id = v2.uploader_id " + 
				"WHERE u1.user_id = ? ";
		queryList.add(vac.getUserId());
		if(vac.getStatus() != -1) {
			sql+="AND v2.status = ? ";
			queryList.add(vac.getStatus());
		}else{
			sql+=" AND v2.status != 2 ";
		}
		if(vac.getCategoryId() != -1) {
			sql+="AND v2.category_id = ? ";
			queryList.add(vac.getStatus());
		}
		if(vac.getSortOrder() != null && !"".equals(vac.getSortOrder())) {
			sql+= "ORDER BY "+vac.getSortOrder()+" DESC ";
		}
		PreparedStatement ps= null;
		ResultSet rs=null;
		int row = 0;
		try {
			ps= conn.prepareStatement(sql);
			for(int i = 0;i<queryList.size();i++) {
				ps.setObject(i+1, queryList.get(i));
			}
			rs = ps.executeQuery();			
			if(rs.next()) {
			    row = rs.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(conn, ps,rs);
		}
		return row;
	}

}
